﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;

namespace DLOrganizer
{
    public abstract class Utilities
    {
        private SqlConnection SQLConnection;
        private SqlCeConnection SQLCEConnection;

        private SqlCommand SQLCommand;
        private SqlCeCommand SQLCECommand;

        private SqlDataAdapter SQLDataAdapter;
        private SqlCeDataAdapter SQLCEDataAdapter;

        private SqlDataReader dr;
        private DataTable DataTable;

        public string Database { get; set; }
        public string StoredProcedure { get; set; }
        public Dictionary<string, object> Parameters { get; set; }

        public Utilities()
        {
            Parameters = new Dictionary<string, object>();
        }

        protected DataTable SQLExecuteDatatable()
        {
            try
            {
                SQLConnection = new SqlConnection(Database);
                SQLCommand = new SqlCommand(StoredProcedure, SQLConnection);
                SQLCommand.CommandType = CommandType.StoredProcedure;
                if (Parameters.Count != 0)
                {
                    foreach (KeyValuePair<string, object> item in Parameters)
                    {
                        SQLCommand.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                DataTable = new DataTable();
                SQLDataAdapter = new SqlDataAdapter(SQLCommand);
                SQLDataAdapter.Fill(DataTable);
                CloseConnection();
                return DataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable SQLCEExecuteDatatable()
        {
            try
            {
                SQLCEConnection = new SqlCeConnection(Database);
                SQLCECommand = new SqlCeCommand(StoredProcedure, SQLCEConnection);
                SQLCEDataAdapter = new SqlCeDataAdapter(SQLCECommand);
                DataTable = new DataTable();
                SQLCEDataAdapter.Fill(DataTable);
                return DataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public SqlDataReader SQLExecuteDataReader()
        {
            try
            {
                SQLConnection = new SqlConnection(Database);
                SQLCommand = new SqlCommand(StoredProcedure, SQLConnection);
                SQLCommand.CommandType = CommandType.StoredProcedure;
                if (Parameters.Count != 0)
                {
                    foreach (KeyValuePair<string, object> item in Parameters)
                    {
                        SQLCommand.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                dr = null;
                SQLConnection.Open();
                dr = SQLCommand.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public void SQLExecuteQuery()
        {
            try
            {
                SQLConnection = new SqlConnection(Database);
                SQLCommand = new SqlCommand(StoredProcedure, SQLConnection);
                SQLCommand.CommandType = CommandType.StoredProcedure;
                if (Parameters.Count != 0)
                {
                    foreach (KeyValuePair<string, object> item in Parameters)
                    {
                        SQLCommand.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                if (SQLConnection.State == ConnectionState.Closed) { SQLConnection.Open(); }
                SQLCommand.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void SQLCEExecuteQuery()
        {
            try
            {
                SQLCEConnection = new SqlCeConnection(Database);
                SQLCECommand = new SqlCeCommand(StoredProcedure, SQLCEConnection);
                if (SQLCEConnection.State == ConnectionState.Closed) { SQLCEConnection.Open(); }
                SQLCECommand.ExecuteNonQuery();
                SQLCEConnection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CloseConnection()
        {
            try
            {
                if (SQLConnection.State == ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}
